{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Здесь находится инструкция API : https://nsi.rosminzdrav.ru/port/swagger-ui.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "\n",
    "\n",
    "# подключаем возможность отключения проверки сертификата\n",
    "from requests.packages.urllib3.exceptions import InsecureRequestWarning\n",
    "requests.packages.urllib3.disable_warnings(InsecureRequestWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as sql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "from database_config import db_postgres\n",
    "\n",
    "host, port, user, password, database = db_postgres()\n",
    "\n",
    "engine = sql.create_engine(f\"postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# личный токен\n",
    "token = 'a0afc06d-12fa-4868-814a-5309de3ea6eb'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Идентификаторы справочников НСИ\n",
    "frmo = '1.2.643.5.1.13.13.11.1461' # Федеральный реестр медицинский организаций\n",
    "frmo_sp = '1.2.643.5.1.13.13.99.2.114' # Справочник структурных подразделений\n",
    "frmo_ok = '1.2.643.5.1.13.13.99.2.115' # Справочник отделений и кабинентов\n",
    "# В случае, если не указана версия справочника, то возвращаются данные из актуальной версии.\n",
    "dictionary_all_3_items = ['1.2.643.5.1.13.13.11.1461', '1.2.643.5.1.13.13.99.2.114', '1.2.643.5.1.13.13.99.2.115']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Запрос выполнен успешно\n"
     ]
    }
   ],
   "source": [
    "# Создаем запрос на проверку статуса подключения\n",
    "url = 'http://nsi.rosminzdrav.ru/'\n",
    "headers = {\n",
    "    'Authorization': f'Bearer {token}'\n",
    "}\n",
    "response = requests.get(url, headers=headers, verify=False)\n",
    "# Проверяем статус ответа\n",
    "if response.status_code == 200:\n",
    "    print('Запрос выполнен успешно')\n",
    "else:\n",
    "    print(f'Ошибка: {response.status_code}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>version</th>\n",
       "      <th>createDate</th>\n",
       "      <th>publishDate</th>\n",
       "      <th>lastUpdate</th>\n",
       "      <th>releaseNotes</th>\n",
       "      <th>archive</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.1266</td>\n",
       "      <td>13.01.2017</td>\n",
       "      <td>15.03.2024 00:16</td>\n",
       "      <td>02.03.2024 00:27</td>\n",
       "      <td>Регистр медицинских организаций Российской Фед...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.1623</td>\n",
       "      <td>22.08.2018</td>\n",
       "      <td>15.03.2024 00:17</td>\n",
       "      <td>23.05.2023 01:22</td>\n",
       "      <td>Справочник структурных подразделений, содержит...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1558</td>\n",
       "      <td>22.08.2018</td>\n",
       "      <td>15.03.2024 00:17</td>\n",
       "      <td>23.05.2023 03:03</td>\n",
       "      <td>Справочник отделений и кабинетов, содержит дан...</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  version  createDate       publishDate        lastUpdate  \\\n",
       "0  6.1266  13.01.2017  15.03.2024 00:16  02.03.2024 00:27   \n",
       "1  2.1623  22.08.2018  15.03.2024 00:17  23.05.2023 01:22   \n",
       "2  2.1558  22.08.2018  15.03.2024 00:17  23.05.2023 03:03   \n",
       "\n",
       "                                        releaseNotes  archive  \n",
       "0  Регистр медицинских организаций Российской Фед...    False  \n",
       "1  Справочник структурных подразделений, содержит...    False  \n",
       "2  Справочник отделений и кабинетов, содержит дан...    False  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# проверка версий справочников\n",
    "url1 = 'http://nsi.rosminzdrav.ru/port/rest/versions'\n",
    "versions = []\n",
    "for items in dictionary_all_3_items:\n",
    "    params = {\n",
    "        'identifier': items,\n",
    "        'size': '1',\n",
    "        'sortingDirection': 'ASC',\n",
    "        'userKey': token\n",
    "    }\n",
    "    response = requests.get(url1, params=params, stream=True)\n",
    "    data = response.json()\n",
    "    versions.append(data)\n",
    "df_hist_dictionary = pd.json_normalize(versions, record_path='list')\n",
    "df_hist_dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_hist_dictionary.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 1:\n",
    "- **mo_oid** ––– OID медицинской организации;\n",
    "- **depart_oid** --- OID структурного подразделения;\n",
    "- **depart_name** ––– Наименование структурного подразделения;\n",
    "- **depart_type_id** ––– Идентификатор типа структурного подразделения;\n",
    "- **depart_create_date** ––– Дата создания записи о структурном подразделении;\n",
    "- **depart_modify_date** –––\tДата изменения записи о структурном подразделении;\n",
    "- **depart_liquidation_date**\t–––\tДата ликвидации структурного подразделения;\n",
    "- **building_address_region_id** ––– Код региона;\n",
    "- **depart_kind_id** ––– Идентификатор вида структурного подразделения."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "556968"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# загрузка справочника \n",
    "url2 = 'http://nsi.rosminzdrav.ru/port/rest/data'\n",
    "params = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['mo_oid', 'depart_oid', 'depart_name', 'depart_type_id', 'depart_create_date', \n",
    "                'depart_modify_date', 'depart_liquidation_date', 'building_address_region_id', 'depart_kind_id']\n",
    "}\n",
    "response = requests.get(url2, params=params, stream=True)\n",
    "data = response.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'column': 'mo_oid', 'value': '1.2.643.5.1.13.13.12.1.11.70064'}, {'column': 'depart_oid', 'value': '1.2.643.5.1.13.13.12.1.11.70064.0.583290'}, {'column': 'depart_name', 'value': 'Медицинский кабинет'}, {'column': 'depart_type_id', 'value': '1'}, {'column': 'depart_create_date', 'value': '2023-01-18T11:17:04.537+0300'}, {'column': 'depart_modify_date', 'value': '2023-07-13T23:00:21.640+0300'}, {'column': 'depart_liquidation_date', 'value': None}, {'column': 'building_address_region_id', 'value': '11'}, {'column': 'depart_kind_id', 'value': '1198'}]\n"
     ]
    }
   ],
   "source": [
    "print(data[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mo_oid</th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>depart_name</th>\n",
       "      <th>depart_type_id</th>\n",
       "      <th>depart_create_date</th>\n",
       "      <th>depart_modify_date</th>\n",
       "      <th>depart_liquidation_date</th>\n",
       "      <th>building_address_region_id</th>\n",
       "      <th>depart_kind_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>Медицинский кабинет</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-18T11:17:04.537+0300</td>\n",
       "      <td>2023-07-13T23:00:21.640+0300</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>1198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>Медицинский работник</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-03-27T04:11:42.436+0300</td>\n",
       "      <td>2023-10-27T14:30:54.320+0300</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>1378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>Медпункт</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-03-23T01:22:49.144+0300</td>\n",
       "      <td>2023-03-29T07:45:47.066+0300</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>1167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>Кабинет стоматолога</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-04-17T04:54:00.178+0300</td>\n",
       "      <td>2023-07-13T23:00:14.931+0300</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>1588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>отделение медицинского сопровождения</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-06T07:08:33.450+0300</td>\n",
       "      <td>2024-02-28T08:34:47.914+0300</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>1494</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            mo_oid                                depart_oid  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064  1.2.643.5.1.13.13.12.1.11.70064.0.583290   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971  1.2.643.5.1.13.13.12.1.14.77971.0.615400   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594  1.2.643.5.1.13.13.12.1.14.78594.0.613597   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078  1.2.643.5.1.13.13.12.1.14.80078.0.626931   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305  1.2.643.5.1.13.13.12.1.14.84305.0.738742   \n",
       "\n",
       "                            depart_name depart_type_id  \\\n",
       "0                   Медицинский кабинет              1   \n",
       "1                  Медицинский работник              6   \n",
       "2                              Медпункт              1   \n",
       "3                   Кабинет стоматолога              1   \n",
       "4  отделение медицинского сопровождения              1   \n",
       "\n",
       "             depart_create_date            depart_modify_date  \\\n",
       "0  2023-01-18T11:17:04.537+0300  2023-07-13T23:00:21.640+0300   \n",
       "1  2023-03-27T04:11:42.436+0300  2023-10-27T14:30:54.320+0300   \n",
       "2  2023-03-23T01:22:49.144+0300  2023-03-29T07:45:47.066+0300   \n",
       "3  2023-04-17T04:54:00.178+0300  2023-07-13T23:00:14.931+0300   \n",
       "4  2024-02-06T07:08:33.450+0300  2024-02-28T08:34:47.914+0300   \n",
       "\n",
       "  depart_liquidation_date building_address_region_id depart_kind_id  \n",
       "0                    None                         11           1198  \n",
       "1                    None                         14           1378  \n",
       "2                    None                         14           1167  \n",
       "3                    None                         14           1588  \n",
       "4                    None                         14           1494  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем пустой список\n",
    "rows = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data)):\n",
    "    dict_data = {d['column']: d['value'] for d in data[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows.append(dict_data)\n",
    "# сохраняем все в датафрейм\n",
    "df_data = pd.DataFrame(rows)\n",
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_data['depart_type_name'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list(df_data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 556968 entries, 0 to 556967\n",
      "Data columns (total 9 columns):\n",
      " #   Column                      Non-Null Count   Dtype \n",
      "---  ------                      --------------   ----- \n",
      " 0   mo_oid                      556968 non-null  object\n",
      " 1   depart_oid                  556968 non-null  object\n",
      " 2   depart_name                 556968 non-null  object\n",
      " 3   depart_type_id              556968 non-null  object\n",
      " 4   depart_create_date          556922 non-null  object\n",
      " 5   depart_modify_date          556968 non-null  object\n",
      " 6   depart_liquidation_date     64648 non-null   object\n",
      " 7   building_address_region_id  494051 non-null  object\n",
      " 8   depart_kind_id              556968 non-null  object\n",
      "dtypes: object(9)\n",
      "memory usage: 38.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_filtered = df_data[df_data['depart_type_id'] == '1']\n",
    "# df_filtered.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data['depart_create_date'] = (df_data['depart_create_date']\n",
    "    .apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))\n",
    "    )\n",
    "df_data['depart_modify_date'] = (df_data['depart_modify_date']\n",
    "    .apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))\n",
    "    )\n",
    "df_data['depart_liquidation_date'] = (df_data['depart_liquidation_date']\n",
    "    .apply(lambda x: pd.to_datetime(x, format='mixed', errors='coerce'))\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 556968 entries, 0 to 556967\n",
      "Data columns (total 9 columns):\n",
      " #   Column                      Non-Null Count   Dtype         \n",
      "---  ------                      --------------   -----         \n",
      " 0   mo_oid                      556968 non-null  object        \n",
      " 1   depart_oid                  556968 non-null  object        \n",
      " 2   depart_name                 556968 non-null  object        \n",
      " 3   depart_type_id              556968 non-null  object        \n",
      " 4   depart_create_date          0 non-null       datetime64[ns]\n",
      " 5   depart_modify_date          0 non-null       datetime64[ns]\n",
      " 6   depart_liquidation_date     0 non-null       datetime64[ns]\n",
      " 7   building_address_region_id  494051 non-null  object        \n",
      " 8   depart_kind_id              556968 non-null  object        \n",
      "dtypes: datetime64[ns](3), object(6)\n",
      "memory usage: 38.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mo_oid</th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>depart_name</th>\n",
       "      <th>depart_type_id</th>\n",
       "      <th>depart_create_date</th>\n",
       "      <th>depart_modify_date</th>\n",
       "      <th>depart_liquidation_date</th>\n",
       "      <th>building_address_region_id</th>\n",
       "      <th>depart_kind_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>Медицинский кабинет</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>11</td>\n",
       "      <td>1198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>Медицинский работник</td>\n",
       "      <td>6</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14</td>\n",
       "      <td>1378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>Медпункт</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14</td>\n",
       "      <td>1167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>Кабинет стоматолога</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14</td>\n",
       "      <td>1588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>отделение медицинского сопровождения</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14</td>\n",
       "      <td>1494</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            mo_oid                                depart_oid  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064  1.2.643.5.1.13.13.12.1.11.70064.0.583290   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971  1.2.643.5.1.13.13.12.1.14.77971.0.615400   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594  1.2.643.5.1.13.13.12.1.14.78594.0.613597   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078  1.2.643.5.1.13.13.12.1.14.80078.0.626931   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305  1.2.643.5.1.13.13.12.1.14.84305.0.738742   \n",
       "\n",
       "                            depart_name depart_type_id depart_create_date  \\\n",
       "0                   Медицинский кабинет              1                NaT   \n",
       "1                  Медицинский работник              6                NaT   \n",
       "2                              Медпункт              1                NaT   \n",
       "3                   Кабинет стоматолога              1                NaT   \n",
       "4  отделение медицинского сопровождения              1                NaT   \n",
       "\n",
       "  depart_modify_date depart_liquidation_date building_address_region_id  \\\n",
       "0                NaT                     NaT                         11   \n",
       "1                NaT                     NaT                         14   \n",
       "2                NaT                     NaT                         14   \n",
       "3                NaT                     NaT                         14   \n",
       "4                NaT                     NaT                         14   \n",
       "\n",
       "  depart_kind_id  \n",
       "0           1198  \n",
       "1           1378  \n",
       "2           1167  \n",
       "3           1588  \n",
       "4           1494  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data['mo_oid'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data['depart_oid'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_filtered['building_address_region_id'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 2 - тип подразделения:\n",
    "- **depart_type_id** ––– Идентификатор типа структурного подразделения;\n",
    "- **depart_type_name** ––– Наименование типа структурного подразделения;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_type_id</th>\n",
       "      <th>depart_type_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Амбулаторный</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Административно-хозяйственный (вспомогательный)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Амбулаторный</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Амбулаторный</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Амбулаторный</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  depart_type_id                                 depart_type_name\n",
       "0              1                                     Амбулаторный\n",
       "1              6  Административно-хозяйственный (вспомогательный)\n",
       "2              1                                     Амбулаторный\n",
       "3              1                                     Амбулаторный\n",
       "4              1                                     Амбулаторный"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params1 = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['depart_type_id', 'depart_type_name']\n",
    "}\n",
    "response1 = requests.get(url2, params=params1, stream=True)\n",
    "data1 = response1.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data1)\n",
    "# создаем пустой список\n",
    "rows1 = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data1)):\n",
    "    dict_data1 = {d['column']: d['value'] for d in data1[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows1.append(dict_data1)\n",
    "# сохраняем все в датафрейм\n",
    "df_data1 = pd.DataFrame(rows1)\n",
    "df_data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_type_id</th>\n",
       "      <th>depart_type_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Амбулаторный</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Административно-хозяйственный (вспомогательный)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Стационарный</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Инструментально-диагностический</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Лабораторно-диагностический</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  depart_type_id                                 depart_type_name\n",
       "0              1                                     Амбулаторный\n",
       "1              6  Административно-хозяйственный (вспомогательный)\n",
       "2              2                                     Стационарный\n",
       "3              4                  Инструментально-диагностический\n",
       "4              3                      Лабораторно-диагностический"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data1 = df_data1.drop_duplicates().reset_index(drop=True)\n",
    "df_data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "depart_type_id      0\n",
       "depart_type_name    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data1.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 3 - регионы:\n",
    "- **building_address_region_id** ––– Код региона;\n",
    "- **building_address_region_name** ––– Наименование региона;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>building_address_region_id</th>\n",
       "      <th>building_address_region_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>Республика Коми</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Республика Саха /Якутия/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Республика Северная Осетия - Алания</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>Республика Татарстан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>Республика Тыва</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  building_address_region_id         building_address_region_name\n",
       "0                         11                      Республика Коми\n",
       "1                         14             Республика Саха /Якутия/\n",
       "2                         15  Республика Северная Осетия - Алания\n",
       "3                         16                 Республика Татарстан\n",
       "4                         17                      Республика Тыва"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params2 = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['building_address_region_id', 'building_address_region_name']\n",
    "}\n",
    "response2 = requests.get(url2, params=params2, stream=True)\n",
    "data2 = response2.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data2)\n",
    "# создаем пустой список\n",
    "rows2 = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data2)):\n",
    "    dict_data2 = {d['column']: d['value'] for d in data2[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows2.append(dict_data2)\n",
    "# сохраняем все в датафрейм\n",
    "df_data2 = pd.DataFrame(rows2)\n",
    "df_data2 = df_data2.drop_duplicates().reset_index(drop=True)\n",
    "df_data2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data2 = df_data2[~df_data2.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>building_address_region_id</th>\n",
       "      <th>building_address_region_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>Республика Коми</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Республика Саха /Якутия/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Республика Северная Осетия - Алания</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>Республика Татарстан</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>Республика Тыва</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>92</td>\n",
       "      <td>Севастополь</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>92</td>\n",
       "      <td>г. Севастополь</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>9</td>\n",
       "      <td>Карачаево-Черкесская Республика</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>95</td>\n",
       "      <td>Херсонская Область</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>99</td>\n",
       "      <td>г. Байконур</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    building_address_region_id         building_address_region_name\n",
       "0                           11                      Республика Коми\n",
       "1                           14             Республика Саха /Якутия/\n",
       "2                           15  Республика Северная Осетия - Алания\n",
       "3                           16                 Республика Татарстан\n",
       "4                           17                      Республика Тыва\n",
       "..                         ...                                  ...\n",
       "169                         92                          Севастополь\n",
       "170                         92                       г. Севастополь\n",
       "171                          9      Карачаево-Черкесская Республика\n",
       "172                         95                   Херсонская Область\n",
       "173                         99                          г. Байконур\n",
       "\n",
       "[174 rows x 2 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data2 = df_data2.rename(columns={'building_address_region_id': 'region_id', \n",
    "                                    'building_address_region_name': 'region_name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data22 = df_data2.drop_duplicates(subset=['region_id'], keep='first').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(90, 2)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data2 = df_data22[~df_data22['region_id'].isnull()]\n",
    "df_data2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 4 - адрес:\n",
    "- **depart_oid** --- OID структурного подразделения;\n",
    "- **building_address_post_index** ––– Почтовый индекс;\n",
    "- **building_address_region_id** ––– Код региона;\n",
    "- **building_address_prefix_area** --- Префикс населенного пункта;\n",
    "- **building_address_area_name** ––– Наименование населенного пункта;\n",
    "- **building_address_prefix_street** ––– Префикс улицы;\n",
    "- **building_address_street_name** ––– Наименование улицы;\n",
    "- **building_address_house** –––\tНомер дома;\n",
    "- **building_address_building**\t–––\tНомер строения;\n",
    "- **building_address_struct** ––– Номер корпуса;\n",
    "- **building_address_latitude** ––– Широта;\n",
    "- **building_address_longtitude** ––– Долгота.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>building_address_post_index</th>\n",
       "      <th>building_address_region_id</th>\n",
       "      <th>building_address_prefix_area</th>\n",
       "      <th>building_address_area_name</th>\n",
       "      <th>building_address_prefix_street</th>\n",
       "      <th>building_address_street_name</th>\n",
       "      <th>building_address_house</th>\n",
       "      <th>building_address_building</th>\n",
       "      <th>building_address_struct</th>\n",
       "      <th>building_address_latitude</th>\n",
       "      <th>building_address_longtitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>169711</td>\n",
       "      <td>11</td>\n",
       "      <td>г</td>\n",
       "      <td>Усинск</td>\n",
       "      <td>ул</td>\n",
       "      <td>60 лет Октября</td>\n",
       "      <td>2А</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>65.987078</td>\n",
       "      <td>57.550946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>678666</td>\n",
       "      <td>14</td>\n",
       "      <td>с</td>\n",
       "      <td>Харбалах</td>\n",
       "      <td>тер.</td>\n",
       "      <td>урочище Кыыртаах</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>62.196199</td>\n",
       "      <td>134.251414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>678300</td>\n",
       "      <td>14</td>\n",
       "      <td>с.п.</td>\n",
       "      <td>Ламынхинский наслег</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>63.785186</td>\n",
       "      <td>139.782188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>г</td>\n",
       "      <td>Якутск</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>62.027221</td>\n",
       "      <td>129.732178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>678200</td>\n",
       "      <td>14</td>\n",
       "      <td>г.п.</td>\n",
       "      <td>город Вилюйск</td>\n",
       "      <td>пер</td>\n",
       "      <td>Партизана Семенова</td>\n",
       "      <td>18/3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>63.745523</td>\n",
       "      <td>121.630856</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 depart_oid building_address_post_index  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064.0.583290                      169711   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971.0.615400                      678666   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594.0.613597                      678300   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078.0.626931                        None   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305.0.738742                      678200   \n",
       "\n",
       "  building_address_region_id building_address_prefix_area  \\\n",
       "0                         11                            г   \n",
       "1                         14                            с   \n",
       "2                         14                         с.п.   \n",
       "3                         14                            г   \n",
       "4                         14                         г.п.   \n",
       "\n",
       "  building_address_area_name building_address_prefix_street  \\\n",
       "0                     Усинск                             ул   \n",
       "1                   Харбалах                           тер.   \n",
       "2        Ламынхинский наслег                           None   \n",
       "3                     Якутск                           None   \n",
       "4              город Вилюйск                            пер   \n",
       "\n",
       "  building_address_street_name building_address_house  \\\n",
       "0               60 лет Октября                     2А   \n",
       "1             урочище Кыыртаах                      2   \n",
       "2                         None                   None   \n",
       "3                         None                   None   \n",
       "4           Партизана Семенова                   18/3   \n",
       "\n",
       "  building_address_building building_address_struct building_address_latitude  \\\n",
       "0                      None                    None                 65.987078   \n",
       "1                      None                    None                 62.196199   \n",
       "2                      None                    None                 63.785186   \n",
       "3                      None                    None                 62.027221   \n",
       "4                      None                    None                 63.745523   \n",
       "\n",
       "  building_address_longtitude  \n",
       "0                   57.550946  \n",
       "1                  134.251414  \n",
       "2                  139.782188  \n",
       "3                  129.732178  \n",
       "4                  121.630856  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params3 = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['depart_oid', 'building_address_post_index', 'building_address_region_id', 'building_address_prefix_area', \n",
    "                'building_address_area_name', 'building_address_prefix_street', 'building_address_street_name',\n",
    "                'building_address_house', 'building_address_building', 'building_address_struct',\n",
    "                'building_address_latitude', 'building_address_longtitude']\n",
    "}\n",
    "response3 = requests.get(url2, params=params3, stream=True)\n",
    "data3 = response3.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data3)\n",
    "# создаем пустой список\n",
    "rows3 = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data3)):\n",
    "    dict_data3 = {d['column']: d['value'] for d in data3[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows3.append(dict_data3)\n",
    "# сохраняем все в датафрейм\n",
    "df_data3 = pd.DataFrame(rows3)\n",
    "df_data3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>post_index</th>\n",
       "      <th>region_id</th>\n",
       "      <th>prefix_area</th>\n",
       "      <th>area_name</th>\n",
       "      <th>prefix_street</th>\n",
       "      <th>street_name</th>\n",
       "      <th>address_house</th>\n",
       "      <th>address_building</th>\n",
       "      <th>address_struct</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>169711</td>\n",
       "      <td>11</td>\n",
       "      <td>г</td>\n",
       "      <td>Усинск</td>\n",
       "      <td>ул</td>\n",
       "      <td>60 лет Октября</td>\n",
       "      <td>2А</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>65.987078</td>\n",
       "      <td>57.550946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>678666</td>\n",
       "      <td>14</td>\n",
       "      <td>с</td>\n",
       "      <td>Харбалах</td>\n",
       "      <td>тер.</td>\n",
       "      <td>урочище Кыыртаах</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>62.196199</td>\n",
       "      <td>134.251414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>678300</td>\n",
       "      <td>14</td>\n",
       "      <td>с.п.</td>\n",
       "      <td>Ламынхинский наслег</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>63.785186</td>\n",
       "      <td>139.782188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>None</td>\n",
       "      <td>14</td>\n",
       "      <td>г</td>\n",
       "      <td>Якутск</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>62.027221</td>\n",
       "      <td>129.732178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>678200</td>\n",
       "      <td>14</td>\n",
       "      <td>г.п.</td>\n",
       "      <td>город Вилюйск</td>\n",
       "      <td>пер</td>\n",
       "      <td>Партизана Семенова</td>\n",
       "      <td>18/3</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>63.745523</td>\n",
       "      <td>121.630856</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 depart_oid post_index region_id prefix_area  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064.0.583290     169711        11           г   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971.0.615400     678666        14           с   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594.0.613597     678300        14        с.п.   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078.0.626931       None        14           г   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305.0.738742     678200        14        г.п.   \n",
       "\n",
       "             area_name prefix_street         street_name address_house  \\\n",
       "0               Усинск            ул      60 лет Октября            2А   \n",
       "1             Харбалах          тер.    урочище Кыыртаах             2   \n",
       "2  Ламынхинский наслег          None                None          None   \n",
       "3               Якутск          None                None          None   \n",
       "4        город Вилюйск           пер  Партизана Семенова          18/3   \n",
       "\n",
       "  address_building address_struct   latitude   longitude  \n",
       "0             None           None  65.987078   57.550946  \n",
       "1             None           None  62.196199  134.251414  \n",
       "2             None           None  63.785186  139.782188  \n",
       "3             None           None  62.027221  129.732178  \n",
       "4             None           None  63.745523  121.630856  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data3 = df_data3.rename(columns={'building_address_post_index': 'post_index', \n",
    "                                    'building_address_region_id': 'region_id',\n",
    "                                    'building_address_prefix_area': 'prefix_area',\n",
    "                                    'building_address_area_name': 'area_name',\n",
    "                                    'building_address_prefix_street': 'prefix_street',\n",
    "                                    'building_address_street_name': 'street_name',\n",
    "                                    'building_address_house': 'address_house',\n",
    "                                    'building_address_building': 'address_building',\n",
    "                                    'building_address_struct': 'address_struct',\n",
    "                                    'building_address_latitude': 'latitude',\n",
    "                                    'building_address_longtitude': 'longitude'})\n",
    "df_data3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "556968"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data3['depart_oid'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "556968"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data3['depart_oid'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 5 - вид подразделения:\n",
    "- **depart_kind_id** --- Идентификатор вида структурного подразделения;\n",
    "- **depart_kind_name** ––– Вид структурного подразделения."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_kind_id</th>\n",
       "      <th>depart_kind_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1198</td>\n",
       "      <td>Прочие</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1378</td>\n",
       "      <td>Отдел медицинского освидетельствования</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1167</td>\n",
       "      <td>Фельдшерские пункты (включая передвижные)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1588</td>\n",
       "      <td>Стоматологический кабинет</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1494</td>\n",
       "      <td>Отделения (пункты, кабинеты)неотложной медицин...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  depart_kind_id                                   depart_kind_name\n",
       "0           1198                                             Прочие\n",
       "1           1378             Отдел медицинского освидетельствования\n",
       "2           1167          Фельдшерские пункты (включая передвижные)\n",
       "3           1588                          Стоматологический кабинет\n",
       "4           1494  Отделения (пункты, кабинеты)неотложной медицин..."
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params4 = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['depart_kind_id', 'depart_kind_name']\n",
    "}\n",
    "response4 = requests.get(url2, params=params4, stream=True)\n",
    "data4 = response4.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data4)\n",
    "# создаем пустой список\n",
    "rows4 = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data4)):\n",
    "    dict_data4 = {d['column']: d['value'] for d in data4[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows4.append(dict_data4)\n",
    "# сохраняем все в датафрейм\n",
    "df_data4 = pd.DataFrame(rows4)\n",
    "df_data4 = df_data4.drop_duplicates().reset_index(drop=True)\n",
    "df_data4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_kind_id</th>\n",
       "      <th>depart_kind_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1198</td>\n",
       "      <td>Прочие</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1378</td>\n",
       "      <td>Отдел медицинского освидетельствования</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1167</td>\n",
       "      <td>Фельдшерские пункты (включая передвижные)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1588</td>\n",
       "      <td>Стоматологический кабинет</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1494</td>\n",
       "      <td>Отделения (пункты, кабинеты)неотложной медицин...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>732</th>\n",
       "      <td>1579</td>\n",
       "      <td>Кабинет экстракорпоральной гемокоррекции и фот...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>733</th>\n",
       "      <td>1663</td>\n",
       "      <td>Кабинет медицинского логопеда</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>1209</td>\n",
       "      <td>Патолого-анатомические в составе бюро судебно-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>1282</td>\n",
       "      <td>Учебный отдел</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>736</th>\n",
       "      <td>1417</td>\n",
       "      <td>Отдел прогнозирования</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>737 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    depart_kind_id                                   depart_kind_name\n",
       "0             1198                                             Прочие\n",
       "1             1378             Отдел медицинского освидетельствования\n",
       "2             1167          Фельдшерские пункты (включая передвижные)\n",
       "3             1588                          Стоматологический кабинет\n",
       "4             1494  Отделения (пункты, кабинеты)неотложной медицин...\n",
       "..             ...                                                ...\n",
       "732           1579  Кабинет экстракорпоральной гемокоррекции и фот...\n",
       "733           1663                      Кабинет медицинского логопеда\n",
       "734           1209  Патолого-анатомические в составе бюро судебно-...\n",
       "735           1282                                      Учебный отдел\n",
       "736           1417                              Отдел прогнозирования\n",
       "\n",
       "[737 rows x 2 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data4 = df_data4[~df_data4.isnull()]\n",
    "df_data4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "714"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data4['depart_kind_id'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>depart_kind_id</th>\n",
       "      <th>depart_kind_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1198</td>\n",
       "      <td>Прочие</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1378</td>\n",
       "      <td>Отдел медицинского освидетельствования</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1167</td>\n",
       "      <td>Фельдшерские пункты (включая передвижные)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1588</td>\n",
       "      <td>Стоматологический кабинет</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1494</td>\n",
       "      <td>Отделения (пункты, кабинеты)неотложной медицин...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>1133</td>\n",
       "      <td>Психоэндокринологические</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>732</th>\n",
       "      <td>1579</td>\n",
       "      <td>Кабинет экстракорпоральной гемокоррекции и фот...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>733</th>\n",
       "      <td>1663</td>\n",
       "      <td>Кабинет медицинского логопеда</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>1282</td>\n",
       "      <td>Учебный отдел</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>736</th>\n",
       "      <td>1417</td>\n",
       "      <td>Отдел прогнозирования</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>714 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    depart_kind_id                                   depart_kind_name\n",
       "0             1198                                             Прочие\n",
       "1             1378             Отдел медицинского освидетельствования\n",
       "2             1167          Фельдшерские пункты (включая передвижные)\n",
       "3             1588                          Стоматологический кабинет\n",
       "4             1494  Отделения (пункты, кабинеты)неотложной медицин...\n",
       "..             ...                                                ...\n",
       "731           1133                           Психоэндокринологические\n",
       "732           1579  Кабинет экстракорпоральной гемокоррекции и фот...\n",
       "733           1663                      Кабинет медицинского логопеда\n",
       "735           1282                                      Учебный отдел\n",
       "736           1417                              Отдел прогнозирования\n",
       "\n",
       "[714 rows x 2 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data4 = df_data4[~df_data4['depart_kind_id'].duplicated()]\n",
    "df_data4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Загрузка справочника структурных подразделений - блок 6 - идентификаторы ГАР:\n",
    "- **depart_oid** ––– OID структурного подразделения;\n",
    "- **building_address_aoid_area** --- Идентификатор населенного пункта;\n",
    "- **building_address_aoid_street** ––– Идентификатор улицы;\n",
    "- **building_address_houseid** ––– Идентификатор дома."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mo_oid</th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>building_address_aoid_area</th>\n",
       "      <th>building_address_aoid_street</th>\n",
       "      <th>building_address_houseid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>267fd50d-0077-4041-9594-18b1de5f2acb</td>\n",
       "      <td>13d1c868-064c-464e-b59c-0f87fb4dfe92</td>\n",
       "      <td>e885bc54-a8c1-4018-87f4-3f75a0e402cb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>292195a1-f3e6-408a-af73-44e90e4b28c5</td>\n",
       "      <td>ceffaad6-3e69-4fad-a11b-1a9493bf9c4b</td>\n",
       "      <td>c715487f-1076-4519-a827-1dd225122e04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>e3241bf2-650e-484f-80aa-c0b634def760</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>884c84a2-0141-4652-962d-8a92989b88f7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>14e7e4bb-9f61-49cf-958c-9a636c12e864</td>\n",
       "      <td>c273ba2f-1fc6-4728-bbd9-fc5889c11d79</td>\n",
       "      <td>f3ee447d-c558-4855-85fe-3265eb5fe114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            mo_oid                                depart_oid  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064  1.2.643.5.1.13.13.12.1.11.70064.0.583290   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971  1.2.643.5.1.13.13.12.1.14.77971.0.615400   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594  1.2.643.5.1.13.13.12.1.14.78594.0.613597   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078  1.2.643.5.1.13.13.12.1.14.80078.0.626931   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305  1.2.643.5.1.13.13.12.1.14.84305.0.738742   \n",
       "\n",
       "             building_address_aoid_area          building_address_aoid_street  \\\n",
       "0  267fd50d-0077-4041-9594-18b1de5f2acb  13d1c868-064c-464e-b59c-0f87fb4dfe92   \n",
       "1  292195a1-f3e6-408a-af73-44e90e4b28c5  ceffaad6-3e69-4fad-a11b-1a9493bf9c4b   \n",
       "2  e3241bf2-650e-484f-80aa-c0b634def760                                  None   \n",
       "3  884c84a2-0141-4652-962d-8a92989b88f7                                  None   \n",
       "4  14e7e4bb-9f61-49cf-958c-9a636c12e864  c273ba2f-1fc6-4728-bbd9-fc5889c11d79   \n",
       "\n",
       "               building_address_houseid  \n",
       "0  e885bc54-a8c1-4018-87f4-3f75a0e402cb  \n",
       "1  c715487f-1076-4519-a827-1dd225122e04  \n",
       "2                                  None  \n",
       "3                                  None  \n",
       "4  f3ee447d-c558-4855-85fe-3265eb5fe114  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "params5 = {\n",
    "    'identifier': frmo_sp,\n",
    "    'size': '1000000',\n",
    "    'sortingDirection': 'ASC',\n",
    "    'userKey': token,\n",
    "    'columns': ['mo_oid', 'depart_oid', 'building_address_aoid_area', 'building_address_aoid_street', 'building_address_houseid']\n",
    "}\n",
    "response5 = requests.get(url2, params=params5, stream=True)\n",
    "data5 = response5.json()['list']\n",
    "# проверяем длину списка\n",
    "len(data5)\n",
    "# создаем пустой список\n",
    "rows5 = []\n",
    "# пройдемся по списку справочников попутно извлекая значения\n",
    "for i in range(len(data5)):\n",
    "    dict_data5 = {d['column']: d['value'] for d in data5[i]}\n",
    "    # дополняем список элементами - элемент списка - одна строка\n",
    "    rows5.append(dict_data5)\n",
    "# сохраняем все в датафрейм\n",
    "df_data5 = pd.DataFrame(rows5)\n",
    "df_data5 = df_data5.drop_duplicates().reset_index(drop=True)\n",
    "df_data5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50484"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data5['building_address_aoid_area'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mo_oid</th>\n",
       "      <th>depart_oid</th>\n",
       "      <th>guid_area</th>\n",
       "      <th>guid_street</th>\n",
       "      <th>guid_house</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.11.70064.0.583290</td>\n",
       "      <td>267fd50d-0077-4041-9594-18b1de5f2acb</td>\n",
       "      <td>13d1c868-064c-464e-b59c-0f87fb4dfe92</td>\n",
       "      <td>e885bc54-a8c1-4018-87f4-3f75a0e402cb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.77971.0.615400</td>\n",
       "      <td>292195a1-f3e6-408a-af73-44e90e4b28c5</td>\n",
       "      <td>ceffaad6-3e69-4fad-a11b-1a9493bf9c4b</td>\n",
       "      <td>c715487f-1076-4519-a827-1dd225122e04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.78594.0.613597</td>\n",
       "      <td>e3241bf2-650e-484f-80aa-c0b634def760</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.80078.0.626931</td>\n",
       "      <td>884c84a2-0141-4652-962d-8a92989b88f7</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305</td>\n",
       "      <td>1.2.643.5.1.13.13.12.1.14.84305.0.738742</td>\n",
       "      <td>14e7e4bb-9f61-49cf-958c-9a636c12e864</td>\n",
       "      <td>c273ba2f-1fc6-4728-bbd9-fc5889c11d79</td>\n",
       "      <td>f3ee447d-c558-4855-85fe-3265eb5fe114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            mo_oid                                depart_oid  \\\n",
       "0  1.2.643.5.1.13.13.12.1.11.70064  1.2.643.5.1.13.13.12.1.11.70064.0.583290   \n",
       "1  1.2.643.5.1.13.13.12.1.14.77971  1.2.643.5.1.13.13.12.1.14.77971.0.615400   \n",
       "2  1.2.643.5.1.13.13.12.1.14.78594  1.2.643.5.1.13.13.12.1.14.78594.0.613597   \n",
       "3  1.2.643.5.1.13.13.12.1.14.80078  1.2.643.5.1.13.13.12.1.14.80078.0.626931   \n",
       "4  1.2.643.5.1.13.13.12.1.14.84305  1.2.643.5.1.13.13.12.1.14.84305.0.738742   \n",
       "\n",
       "                              guid_area                           guid_street  \\\n",
       "0  267fd50d-0077-4041-9594-18b1de5f2acb  13d1c868-064c-464e-b59c-0f87fb4dfe92   \n",
       "1  292195a1-f3e6-408a-af73-44e90e4b28c5  ceffaad6-3e69-4fad-a11b-1a9493bf9c4b   \n",
       "2  e3241bf2-650e-484f-80aa-c0b634def760                                  None   \n",
       "3  884c84a2-0141-4652-962d-8a92989b88f7                                  None   \n",
       "4  14e7e4bb-9f61-49cf-958c-9a636c12e864  c273ba2f-1fc6-4728-bbd9-fc5889c11d79   \n",
       "\n",
       "                             guid_house  \n",
       "0  e885bc54-a8c1-4018-87f4-3f75a0e402cb  \n",
       "1  c715487f-1076-4519-a827-1dd225122e04  \n",
       "2                                  None  \n",
       "3                                  None  \n",
       "4  f3ee447d-c558-4855-85fe-3265eb5fe114  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data5 = df_data5.rename(columns={'building_address_aoid_area': 'guid_area', 'building_address_aoid_street': 'guid_street',\n",
    "                                    'building_address_houseid': 'guid_house'})\n",
    "df_data5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70911"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data5['mo_oid'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "556968"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data5['depart_oid'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "556968"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data.to_sql('register_depart', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n",
    "df_data1.to_sql('depart_type', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n",
    "df_data2.to_sql('region', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n",
    "df_data3.to_sql('depart_address', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n",
    "df_data4.to_sql('depart_kind', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')\n",
    "df_data5.to_sql('depart_addr_guid', engine, if_exists='replace', index=False, chunksize=1000, schema='db01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_data.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/register_depart.csv', sep=';', index=False)\n",
    "# df_data1.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/depart_type.csv', sep=';', index=False)\n",
    "# df_data2.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/region.csv', sep=';', index=False)\n",
    "# df_data3.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/depart_address.csv', sep=';', index=False)\n",
    "# df_data4.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/depart_kind.csv', sep=';', index=False)\n",
    "# df_data5.to_csv('C:/Users/GlazkovOI/minzdrav/data/register_department/depart_addr_guid.csv', sep=';', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 556968 entries, 0 to 556967\n",
      "Data columns (total 9 columns):\n",
      " #   Column                      Non-Null Count   Dtype         \n",
      "---  ------                      --------------   -----         \n",
      " 0   mo_oid                      556968 non-null  object        \n",
      " 1   depart_oid                  556968 non-null  object        \n",
      " 2   depart_name                 556968 non-null  object        \n",
      " 3   depart_type_id              556968 non-null  object        \n",
      " 4   depart_create_date          0 non-null       datetime64[ns]\n",
      " 5   depart_modify_date          0 non-null       datetime64[ns]\n",
      " 6   depart_liquidation_date     0 non-null       datetime64[ns]\n",
      " 7   building_address_region_id  494051 non-null  object        \n",
      " 8   depart_kind_id              556968 non-null  object        \n",
      "dtypes: datetime64[ns](3), object(6)\n",
      "memory usage: 38.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7 entries, 0 to 6\n",
      "Data columns (total 2 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   depart_type_id    7 non-null      object\n",
      " 1   depart_type_name  7 non-null      object\n",
      "dtypes: object(2)\n",
      "memory usage: 240.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df_data1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 90 entries, 0 to 90\n",
      "Data columns (total 2 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   region_id    90 non-null     object\n",
      " 1   region_name  90 non-null     object\n",
      "dtypes: object(2)\n",
      "memory usage: 2.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df_data2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 556968 entries, 0 to 556967\n",
      "Data columns (total 12 columns):\n",
      " #   Column            Non-Null Count   Dtype \n",
      "---  ------            --------------   ----- \n",
      " 0   depart_oid        556968 non-null  object\n",
      " 1   post_index        379902 non-null  object\n",
      " 2   region_id         494051 non-null  object\n",
      " 3   prefix_area       445843 non-null  object\n",
      " 4   area_name         445853 non-null  object\n",
      " 5   prefix_street     482016 non-null  object\n",
      " 6   street_name       482133 non-null  object\n",
      " 7   address_house     476547 non-null  object\n",
      " 8   address_building  60280 non-null   object\n",
      " 9   address_struct    8199 non-null    object\n",
      " 10  latitude          494051 non-null  object\n",
      " 11  longitude         494051 non-null  object\n",
      "dtypes: object(12)\n",
      "memory usage: 51.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 714 entries, 0 to 736\n",
      "Data columns (total 2 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   depart_kind_id    714 non-null    object\n",
      " 1   depart_kind_name  714 non-null    object\n",
      "dtypes: object(2)\n",
      "memory usage: 16.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df_data4.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 556968 entries, 0 to 556967\n",
      "Data columns (total 5 columns):\n",
      " #   Column       Non-Null Count   Dtype \n",
      "---  ------       --------------   ----- \n",
      " 0   mo_oid       556968 non-null  object\n",
      " 1   depart_oid   556968 non-null  object\n",
      " 2   guid_area    424188 non-null  object\n",
      " 3   guid_street  459231 non-null  object\n",
      " 4   guid_house   453982 non-null  object\n",
      "dtypes: object(5)\n",
      "memory usage: 21.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df_data5.info()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
